//
//  SQLTool.m
//  trj
//
//  Created by 小小算盘 on 16/1/20.
//  Copyright © 2016年 小小算盘. All rights reserved.
//

#import "SQLTool.h"
#import "FMDB.h"


#define cachePath [NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES) lastObject]

static FMDatabaseQueue *_queue;
@implementation SQLTool
/**
 *  创建数据库表，用于缓存数据
 */
+(void)initialize
{
    NSString *bundleName = [[NSBundle mainBundle] infoDictionary][(NSString *)kCFBundleNameKey];
    NSString *dbName = [NSString stringWithFormat:@"%@%@",bundleName,@".sqlite"];
    NSString *fileName = [cachePath stringByAppendingPathComponent:dbName];
    NSLog(@"%@",fileName);
    //_db = [FMDatabase databaseWithPath:fileName];
    _queue = [FMDatabaseQueue databaseQueueWithPath:fileName];
    //创建登录信息表
    [_queue inDatabase:^(FMDatabase *db) {

        BOOL successLogin = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS Login_table (idx integer primary key autoincrement,key text NOT NULL,oid_partner text NOT NULL, paypassword text NOT NULL,phone text NOT NULL,real_status text NOT NULL,realname text NOT NULL,access_id text NOT NULL,access_key text NOT NULL,des_key text NOT NULL,username text NOT NULL)"];
        
        NSLog(@"%@",successLogin ?@"创建登录信息表成功":@"创建登录信息表失败");
        //推荐标表格
        BOOL successRecommend = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS Recommend_table (abbreviation text NOT NULL, apr text NOT NULL, id text NOT NULL,unit text NOT NULL,scales text NOT NULL,project_value text NOT NULL,invest_minimum text NOT NULL,num text NOT NULL,borrow_name text NOT NULL);"];
        NSLog(@"%@",successRecommend ?@"创建推荐表成功":@"创建推荐表失败");
        
       BOOL success = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS Borrow_listTable(idx integer primary key autoincrement,borrow_list bolb);"];
        NSLog(@"%@",success ?@"创建标列表表成功":@"创建标列表表失败");
        
        BOOL successAccount = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS AccountMoney_table(collection text NOT NULL, no_use_money text NOT NULL, taste_money text NOT NULL,total text NOT NULL,total_interest text NOT NULL,use_money text NOT NULL);"];
        NSLog(@"%@",successAccount ?@"创建账户money表成功":@"创建账户money表失败");
        
        BOOL successInvestTotal = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS InvestTotalMoney_table(borrow_hits text NOT NULL, borrow_money text NOT NULL, overdue_hits text NOT NULL,violate_hits text NOT NULL);"];
        NSLog(@"%@",successInvestTotal ?@"创建投资总额表成功":@"创建投资总额表失败");
        
        BOOL UserInfo = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS UserInfo_table(card_id text NOT NULL, paypassword text NOT NULL, realname text NOT NULL,username text NOT NULL);"];
        NSLog(@"%@",UserInfo ?@"创建用户信息表成功":@"创建用户信息表失败");
        
    }];
    
}
/**
 *  保存用户信息到数据库
 *
 *  @param dic 用户信息
 */
+(void)saveUserInfoWithDic:(NSDictionary *)dic
{
    [_queue inDatabase:^(FMDatabase *db) {
        BOOL successU = [db executeUpdateWithFormat:@"INSERT INTO UserInfo_table(card_id,paypassword,realname,username)VALUES(%@,%@,%@,%@);",[dic[@"data"][@"card_id"] isKindOfClass:[NSNull class]]?@"":dic[@"data"][@"card_id"],[dic[@"data"][@"paypassword"] isKindOfClass:[NSNull class]]?@"":dic[@"data"][@"paypassword"],[dic[@"data"][@"realname"] isKindOfClass:[NSNull class]]?@"":dic[@"data"][@"realname"],[dic[@"data"][@"username"] isKindOfClass:[NSNull class]]?@"":dic[@"data"][@"username"]];
        
        NSLog(@"%@",successU?@"保存用户信息表成功":@"保存用户信息表失败");
    }];

}
/**
 *  查询用户信息并返回字典
 *
 *  @return 用户信息
 */
+(NSDictionary *)searchUserInfoWithSQL
{
    __block NSDictionary *dic = [NSDictionary dictionary];
    [_queue inDatabase:^(FMDatabase *db) {
        FMResultSet *set = [db executeQuery:@"SELECT * FROM UserInfo_table"];
        while ([set next]) {
            dic = [NSDictionary dictionaryWithObjectsAndKeys:[set stringForColumn:@"card_id"],@"card_id",[set stringForColumn:@"paypassword"],@"paypassword",[set stringForColumn:@"realname"],@"realname",[set stringForColumn:@"username"],@"username", nil];
        }
    }];
    
    return dic;
}
/**
 *  更新用户信息表
 *
 *  @param dic 用户信息
 */
+(void)updateUserInfo_tableWithDic:(NSDictionary *)dic
{
    [_queue inDatabase:^(FMDatabase *db) {
        BOOL success = [db executeUpdate:@"UPDATE UserInfo_table SET card_id =?,paypassword =?,realname =?,username =?;",dic[@"data"][@"card_id"],dic[@"data"][@"paypassword"],dic[@"data"][@"realname"],dic[@"data"][@"username"]];
        
        NSLog(@"%@",success ?@"更新用户信息表成功":@"更新用户信息表失败");
    }];
}

/**
 *  保存登录信息到数据库
 *
 *  @param dic 登录信息
 */
+(void)saveLoginMessageWithDic:(NSDictionary *)dic
{
    [_queue inDatabase:^(FMDatabase *db) {
        
        BOOL sucessL = [db executeUpdateWithFormat:@"INSERT INTO Login_table(key,oid_partner,paypassword,phone,real_status,realname,access_id,access_key,des_key,username)VALUES(%@,%@,%@,%@,%@,%@,%@,%@,%@,%@);",dic[@"data"][@"key"],dic[@"data"][@"oid_partner"],dic[@"data"][@"paypassword"],dic[@"data"][@"phone"],dic[@"data"][@"real_status"],dic[@"data"][@"realname"],dic[@"data"][@"token"][@"access_id"],dic[@"data"][@"token"][@"access_key"],dic[@"data"][@"token"][@"des_key"],dic[@"data"][@"username"]];
        NSLog(@"%@",sucessL ?@"保存登录信息表成功":@"保存登录信息表失败");
        
    }];
    
}
/**
 *  查询用户信息及连连商户号，返回一个字典
 *
 *  @return 登录信息
 */
+(NSDictionary *)searchLoginMessageFindUserWithSQl
{
    __block NSDictionary *dict = [NSDictionary dictionary];
    [_queue inDatabase:^(FMDatabase *db) {
        
        FMResultSet *result = [db executeQuery:@"SELECT *FROM Login_table"];
        while ([result next]) {
            dict = [NSDictionary dictionaryWithObjectsAndKeys:[result stringForColumn:@"key"],@"LianLianKey",[result stringForColumn:@"oid_partner"],@"LianLianOid_Partner",[result stringForColumn:@"phone"],@"LianLianPhone",[result stringForColumn:@"realname"],@"LianLianRealName",[result stringForColumn:@"paypassword"],@"PayPassWord",[result stringForColumn:@"real_status"],@"Real_Status", nil];
        }
        
    }];
    
    return dict;
}
/**
 *  更新是否设置了支付密码
 *
 *  @param str 是否设置支付密码的状态
 */
+(void)updateLogin_tableWithPaypassword:(NSString *)str
{
    [_queue inDatabase:^(FMDatabase *db) {
        BOOL success = [db executeUpdate:@"UPDATE Login_table SET access_id =?;",str];
        NSLog(@"%@",success ?@"更新设置密码状态成功":@"更新设置密码状态失败");
    }];

}
/**
 *  更新登录信息数据库
 只做部分数据的跟新，此方法待用
 *
 *  @param dic
 */
+(void)upDataWithLoginMessageWithDic:(NSDictionary *)dic
{
    [_queue inDatabase:^(FMDatabase *db) {
        
        
        
        BOOL success = [db executeUpdate:@"UPDATE Login_table SET key =?,oid_partner =?,paypassword =?,phone =?,real_status = ?,realname = ?,access_id = ?,access_key = ?,des_key = ?,username = ?;",dic[@"data"][@"key"],dic[@"data"][@"oid_partner"],dic[@"data"][@"paypassword"],dic[@"data"][@"phone"],dic[@"data"][@"real_status"],dic[@"data"][@"realname"],dic[@"data"][@"token"][@"access_id"],dic[@"data"][@"token"][@"access_key"],dic[@"data"][@"token"][@"des_key"],dic[@"data"][@"username"]];
        
        NSLog(@"%@",success ?@"更新表成功":@"更新表失败");
        
    }];
    
}
/**
 *  实名认证之后更新状态
 *
 *  @param str    实名认证状态
 *  @param string 真实姓名
 */
+(void)updataWithLoginMessageWithStr:(NSString *)str andRealName:(NSString *)string
{
    [_queue inDatabase:^(FMDatabase *db) {
        [db executeUpdate:@"UPDATE Login_table SET real_status = ?,realname = ?;",str,string];
    }];
}
/**
 *  保存推荐标数据表
 *
 *  @param dic 推荐标
 */
+(void)saveRecommentWithDic:(NSDictionary *)dic
{
    [_queue inDatabase:^(FMDatabase *db) {
        
        
        
        BOOL success = [db executeUpdateWithFormat:@"INSERT INTO Recommend_table(abbreviation,apr,id,unit,scales,project_value,invest_minimum,num,borrow_name)VALUES(%@,%@,%@,%@,%@,%@,%@,%@,%@);",[dic[@"data"] firstObject][@"abbreviation"] ,[dic[@"data"] firstObject][@"apr"],[dic[@"data"] firstObject][@"id"],[dic[@"data"] firstObject][@"loan_period"][@"unit"],[dic[@"data"] firstObject][@"scales"],[dic[@"data"] firstObject][@"project_value"],[dic[@"data"] firstObject][@"invest_minimum"],[dic[@"data"] firstObject][@"loan_period"][@"num"],[dic[@"data"] firstObject][@"borrow_name"]];
        NSLog(@"%@",success ?@"插入数据成功":@"插入数据失败");
        
    }];
    
}
/**
 *  查询数据库，取出数据
 *
 *  @return 推荐标
 */
+(NSDictionary *)search{
    __block NSDictionary *dic = [NSDictionary dictionary];
    [_queue inDatabase:^(FMDatabase *db) {
        FMResultSet *result = [db executeQuery:@"SELECT *FROM Recommend_table"];
        
        while ([result next]) {
            NSString *abbreviation = [result stringForColumn:@"abbreviation"];
            NSString *apr = [result stringForColumn:@"apr"];
            NSString *ID = [result stringForColumn:@"id"];
            NSString *unit = [result stringForColumn:@"unit"];
            NSString *scales = [result stringForColumn:@"scales"];
            NSString *project_value = [result stringForColumn:@"project_value"];
            NSString *invest_minimum = [result stringForColumn:@"invest_minimum"];
            NSString *num = [result stringForColumn:@"num"];
            NSString *borrow_name = [result stringForColumn:@"borrow_name"];
            dic = [NSDictionary dictionaryWithObjectsAndKeys:abbreviation,@"abbreviation",apr,@"apr",ID,@"ID",unit,@"unit",scales,@"scales",project_value,@"project_value",invest_minimum,@"invest_minimum",num,@"num",borrow_name,@"borrow_name" ,nil];
            
        }
        
    }];
    return dic;
}
/**
 *  更新数据库
 *
 *  @param dic 推荐标
 */
+(void)updataRecommentWithDic:(NSDictionary *)dic
{
    [_queue inDatabase:^(FMDatabase *db) {
        BOOL success = [db executeUpdate:@"UPDATE Recommend_table SET abbreviation =?,apr =?,id =?,unit =?,scales = ?,project_value = ?,invest_minimum = ?,num = ?,borrow_name =?;",[dic[@"data"] firstObject][@"abbreviation"],[dic[@"data"] firstObject][@"apr"],[dic[@"data"] firstObject][@"id"],[dic[@"data"] firstObject][@"loan_period"][@"unit"],[dic[@"data"] firstObject][@"scales"],[dic[@"data"] firstObject][@"project_value"],[dic[@"data"] firstObject][@"invest_minimum"],[dic[@"data"] firstObject][@"loan_period"][@"num"],[dic[@"data"] firstObject][@"borrow_name"]];
        
        NSLog(@"%@",success ?@"更新表成功":@"更新表失败");
        
        
    }];
}

/**
 *  保存标列表信息
 *
 *  @param dic 标列表
 */
+(void)saveBorrowLiastMessageWithArr:(NSArray *)arr
{
    NSData *data= [NSKeyedArchiver archivedDataWithRootObject:arr];
    [_queue inDatabase:^(FMDatabase *db) {
        [db executeUpdateWithFormat:@"INSERT INTO Borrow_listTable(borrow_list)VALUES(%@);",data];
    }];
}

/**
 *  查询标列表信息
 *
 *  @return 标列表
 */
+(NSArray *)searchBorrowListWithSql
{
    __block NSArray *arr = [NSArray array];
    [_queue inDatabase:^(FMDatabase *db) {
        FMResultSet *RESLUT =  [db executeQuery:@"SELECT *FROM Borrow_listTable"];
        while (RESLUT.next) {
            NSData *data = [RESLUT dataForColumn:@"borrow_list"];
            arr = [NSKeyedUnarchiver unarchiveObjectWithData:data];
            
        }
    }];
    return arr;
}

/**
 *  更新标列表
 *
 *  @param dic 标列表
 */
+(void)updataBorrowListMessageWithArr:(NSArray *)arr
{
    NSData *data = [NSKeyedArchiver archivedDataWithRootObject:arr];
    [_queue inDatabase:^(FMDatabase *db) {
        [db executeUpdate:@"UPDATE Borrow_listTable SET borrow_list =?;",data];
    }];
    
}
/**
 *  保存账户money
 *
 *  @param dic 账户
 */
+(void)saveAccountMoneyWithDic:(NSDictionary *)dic
{
    [_queue inDatabase:^(FMDatabase *db) {
        [db executeUpdateWithFormat:@"INSERT INTO AccountMoney_table(collection, no_use_money , taste_money ,total ,total_interest ,use_money )VALUES (%@,%@,%@,%@,%@,%@);",dic[@"data"][@"collection"],dic[@"data"][@"no_use_money"],dic[@"data"][@"taste_money"],dic[@"data"][@"total"],dic[@"data"][@"total_interest"],dic[@"data"][@"use_money"]];
    }];
    
}
/**
 *  查询帐户money
 *
 *  @return 账户
 */
+(NSDictionary *)searchAccountSql
{
    __block NSDictionary *dict = [NSDictionary dictionary];
    [_queue inDatabase:^(FMDatabase *db) {
        FMResultSet *result = [db executeQuery:@"SELECT *FROM AccountMoney_table"];
        while (result.next) {
            dict = [NSDictionary dictionaryWithObjectsAndKeys:[result stringForColumn:@"collection"],@"collection", [result stringForColumn:@"no_use_money"],@"no_use_money",[result stringForColumn:@"taste_money"],@"taste_money",[result stringForColumn:@"total"],@"total",[result stringForColumn:@"total_interest"],@"total_interest",[result stringForColumn:@"use_money"],@"use_money",nil];
        }
    }];
    return dict;
}
/**
 *  更新账户money
 *
 *  @param dic 账户
 */
+(void)updataAccountMoneyWith:(NSDictionary *)dic
{
    [_queue inDatabase:^(FMDatabase *db) {
        [db executeUpdate:@"UPDATE AccountMoney_table SET collection =?, no_use_money=? , taste_money =?,total =?,total_interest =?,use_money=? ;",dic[@"data"][@"collection"],dic[@"data"][@"no_use_money"],dic[@"data"][@"taste_money"],dic[@"data"][@"total"],dic[@"data"][@"total_interest"],dic[@"data"][@"use_money"]];
    }];
    
}
/**
 *  保存投资总额到数据库
 *
 *  @param dic 投资总额
 */
+(void)saveInvestTotalMoneyWithDic:(NSDictionary *)dic
{
    
    [_queue inDatabase:^(FMDatabase *db) {
        [db executeUpdateWithFormat:@"INSERT INTO InvestTotalMoney_table(borrow_hits , borrow_money , overdue_hits ,violate_hits )VALUES (%@,%@,%@,%@);",dic[@"data"][@"borrow_hits"],dic[@"data"][@"borrow_money"],dic[@"data"][@"overdue_hits"],dic[@"data"][@"violate_hits"]];
    }];
}
/**
 *  查询投资总额
 */
+(NSString *)searchInvestTotalMoney
{
    __block NSString *str = @"";
    [_queue inDatabase:^(FMDatabase *db) {
        FMResultSet *result = [db executeQuery:@"SELECT *FROM InvestTotalMoney_table"];
        while (result.next) {
            str = [result stringForColumn:@"borrow_money"];
        }
    }];
    return str;
}
/**
 *  更新投资总额表里的投资总额
 *
 *  @param str 投资总额
 */
+(void)updataInvestTotalMoneyWithStr:(NSString *)str{
    [_queue inDatabase:^(FMDatabase *db) {
        [db executeUpdate:@"UPDATE InvestTotalMoney_table SET borrow_money =?;",str];
    }];

}
@end
